package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IFileInfoDao;
import com.boss.salary.entity.FileInfo;
import com.boss.sys.utils.SysUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;
@Repository
public class FileInfoDaoImpl extends BaseDaoImpl implements IFileInfoDao {
    @Override
    public void insertFileInfo(FileInfo fileInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into ").append(TableName.SAL_FILE_INFO)
           .append(" (id, biz_key, billguid, create_time, update_time, agency_id, salary_month, file_id, middle_table_code,")
           .append(" middle_id, middle_time, file_name, file_type, is_deleted, mof_div_code)")
           .append(" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        Object[] params = new Object[]{
                fileInfo.getId(), fileInfo.getBizKey(), fileInfo.getBillguid(), fileInfo.getCreateTime(),
                fileInfo.getUpdateTime(), fileInfo.getAgencyId(), fileInfo.getSalaryMonth(), fileInfo.getFileId(), fileInfo.getMiddleTableCode(), fileInfo.getMiddleId(),
                fileInfo.getMiddleTime(), fileInfo.getFileName(), fileInfo.getFileType(), fileInfo.getIsDeleted(),fileInfo.getMofDivCode()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void deleteFileInfo(String id) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.SAL_FILE_INFO);
        sql.append(" set is_deleted=? ")
                .append("where id=?");
        Object[] params = new Object[]{
                SystemDictConstant.IS_TRUE, id
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }



    //批量删除
    @Override
    public void deleteEnclosureByIds(List<String> id) {
        List<String> paramList = new ArrayList<>();
        for(String ByIds:id) {
            paramList.add(ByIds);
        }
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.SAL_FILE_INFO);
        sql.append(" set is_deleted='1' ")
                .append("where ");
        sql.append(SysUtils.inSql("id",paramList));
            this.getJdbcTemplate().update(sql.toString());

    }

    @Override
    public List<FileInfo> getFileInfo(List<String> personIds, String operateType,String agencyId,String mofDivCode,String salary_month) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select a.id, a.biz_key, a.billguid, a.create_time, a.update_time, a.file_id,b.person_name middle_table_code,")
           .append(" a.middle_id, a.middle_time, a.file_name, a.file_type, a.is_deleted from ").append(TableName.SAL_FILE_INFO).append(" a ")
           .append(" join ")
           .append(TableName.PERSON_EXT).append(" b ")
           .append(" on a.middle_id=b.person_id ")
           .append(" where a.is_deleted=? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        if(!CollectionUtils.isEmpty(personIds)) {
            sql.append(" and ").append(SysUtils.inSql("a.middle_id", personIds));
        }
        sql.append(" and a.mof_div_code=? and a.middle_table_code=? ");
        paramList.add(mofDivCode);
        paramList.add(operateType);
        // 单位查询时，按照单位id查询
        sql.append(" and a.agency_id = ?");
        paramList.add(agencyId);
        sql.append(" and a.salary_month = ?");
        paramList.add(salary_month);
        sql.append(" ORDER BY  middle_table_code ,a.middle_time DESC ");
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(FileInfo.class), paramList.toArray());
    }

    @Override
    public Integer getFileInfoByFileId(String fileid,String mofDivCode) {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) from ").append(TableName.SAL_FILE_INFO)
           .append(" where is_deleted=? and file_id =? and mof_div_code=? ");
        Object[] params = new Object[]{
                SystemDictConstant.IS_FALSE, fileid,mofDivCode
        };
        return this.getJdbcTemplate().queryForObject(sql.toString(),Integer.class,params);
    }

    @Override
    public int queryPersonFileNum(String id, String agencyId, String operateType,String mofDivCode) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT count(*) FROM ")
                .append(TableName.SAL_FILE_INFO).append(" t")
                .append(" JOIN ")
                .append(TableName.AGENCY_VERIFY).append(" sav ")
                .append("ON ( t.agency_id = sav.agency_id AND sav.is_month_end = 0 AND t.middle_table_code = '2')")
                .append( " WHERE t.salary_month BETWEEN sav.salary_month AND concat(sav.salary_month, '31') ")
                .append(" AND t.is_deleted = 0 AND t.middle_id = ?");
        paramList.add(id);
        sql.append(" and t.mof_div_code=?");
        paramList.add(mofDivCode);
        if ("2".equals(operateType)) {
            sql.append(" and t.agency_id = ?");
            paramList.add(agencyId);
        }
        return this.getJdbcTemplate().queryForObject(sql.toString(),Integer.class,paramList.toArray());
    }

    @Override
    public List<FileInfo> getFileInfoByAgencyId(String agencyId,String mofDivCode,String salary_month) {
        StringBuilder sql = new StringBuilder();
        sql.append("select a.id, a.biz_key, a.billguid, a.create_time, a.update_time, a.file_id,b.agency_name middle_table_code,")
                .append(" a.middle_id, a.middle_time, a.file_name, a.file_type, a.is_deleted from ").append(TableName.SAL_FILE_INFO).append(" a ")
                .append(" join ")
                .append(TableName.AGENCY_INFO).append(" b ")
                .append(" on a.agency_id=b.agency_id ")
                .append(" where a.is_deleted=? and a.middle_table_code = 1 and ")
                .append(" a.agency_id = ? and a.mof_div_code=?")
                .append(" a.salary_month=? ");
        Object[] params = new Object[]{
                SystemDictConstant.IS_FALSE,agencyId,mofDivCode,salary_month
        };
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(FileInfo.class), params);
    }

    @Override
    public int selectHaveFilePersonCount(String agencyId, String salaryMonth, List<String> personIdList) {
        if(CollectionUtils.isEmpty(personIdList)){
            return 0;
        }
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select count(*) from ( ")
                .append(" select DISTINCT middle_id from ")
                .append(TableName.SAL_FILE_INFO)
                .append(" where ").append(" is_deleted = 0 and middle_table_code = '2' ")
                .append(" and salary_month = ? and agency_id = ? and  ").append(SysUtils.inSql("middle_id",personIdList));
        paramList.add(salaryMonth);
        paramList.add(agencyId);
        sb.append(" ) t ");
        return super.getjdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), int.class);
    }

    @Override
    public List<FileInfo> queryExcel(List<String> personIds, String agencyId, String mofDivCode, String salary_month) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select a.id, a.biz_key, a.billguid, a.create_time, a.update_time, a.file_id,b.person_name middle_table_code,")
                .append(" a.middle_id, a.middle_time, a.file_name, a.file_type, a.is_deleted from ").append(TableName.SAL_FILE_INFO).append(" a ")
                .append(" join ")
                .append(TableName.PERSON_EXT).append(" b ")
                .append(" on a.middle_id=b.person_id ")
                .append(" where a.is_deleted=? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        if (!CollectionUtils.isEmpty(personIds)) {
            sql.append(" and ").append(SysUtils.inSql("a.middle_id", personIds));
        }
        sql.append(" and a.mof_div_code=? ");
        paramList.add(mofDivCode);
        // 单位查询时，按照单位id查询
        sql.append(" and a.agency_id = ?");
        paramList.add(agencyId);
        sql.append(" and a.salary_month = ?");
        paramList.add(salary_month);

        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(FileInfo.class), paramList.toArray());
    }

}
